package com.test.dao;

import com.test.entity.CompAllInfoDto;
import com.test.entity.NewsPortalDto;
import com.test.entity.StuAllInfoDto;
import com.test.entity.TeaAllInfoDto;

import java.util.Date;

public class ManagerOperatProvider {

    public String selectAlltea(TeaAllInfoDto teaAllInfoDto){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "\ttu.u_acount,\n" +
                "tti.tea_code,\n" +
                "tti.tea_department,\n" +
                "tti.tea_duties,\n" +
                "tti.tea_email,\n" +
                "tti.tea_name,\n" +
                "tti.tea_qq,\n" +
                "tu.u_status,\n" +
                "tu.u_acount,\n" +
                "tti.tea_schoolname,\n" +
                "tti.tea_tel\n" +
                "FROM\n" +
                "\ttable_user tu\n" +
                "RIGHT JOIN table_teabase_info tti on tu.u_acount = tti.u_acount\n" +
                "where u_Identity = '2'");

        if (teaAllInfoDto.getTea_code()!=null&&!"".equals(teaAllInfoDto.getTea_code())){
            sql.append(" and  tti.tea_code like '%"+teaAllInfoDto.getTea_code()+"%'");
        }
        if (teaAllInfoDto.getTea_name()!=null&&!"".equals(teaAllInfoDto.getTea_name())){
            sql.append(" and  tti.tea_name like '%"+teaAllInfoDto.getTea_name()+"%'");
        }
        if (teaAllInfoDto.getTea_department()!=null&&!"".equals(teaAllInfoDto.getTea_department())){
            sql.append(" and  tti.tea_department like '%"+teaAllInfoDto.getTea_department()+"%'");
        }

        return sql.toString();
    }


    public String selectAllcomp(CompAllInfoDto compAllInfoDto){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "\ttu.u_acount,\n" +
                "tu.u_status,\n" +
                "tcbi.comp_address,\n" +
                "tcbi.comp_city,\n" +
                "tcbi.comp_code,\n" +
                "tcbi.comp_contact,\n" +
                "tcbi.comp_contacttel,\n" +
                "tcbi.comp_county,\n" +
                "tcbi.comp_email,\n" +
                "tcbi.comp_kind,\n" +
                "tcbi.comp_name,\n" +
                "tcbi.comp_nature,\n" +
                "tcbi.comp_province\n" +
                "\n" +
                "FROM\n" +
                "\ttable_user tu\n" +
                "LEFT JOIN table_compbase_info tcbi on tu.u_acount = tcbi.u_acount\n" +
                "WHERE\n" +
                "tu.u_Identity = '4' and  comp_name is not null");

        if (compAllInfoDto.getComp_name()!=null&&!"".equals(compAllInfoDto.getComp_name())){
            sql.append(" and  tcbi.comp_name like '%"+compAllInfoDto.getComp_name()+"%'");
        }
        if (compAllInfoDto.getComp_contact()!=null&&!"".equals(compAllInfoDto.getComp_contact())){
            sql.append(" and  tcbi.comp_contact like '%"+compAllInfoDto.getComp_contact()+"%'");
        }
        return sql.toString();
    }


    public String selectCompByCode(String u_acount){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "\ttu.u_acount,\n" +
                "tu.u_status,\n" +
                "tcbi.comp_address,\n" +
                "tcbi.comp_city,\n" +
                "tcbi.comp_code,\n" +
                "tcbi.comp_contact,\n" +
                "tcbi.comp_contacttel,\n" +
                "tcbi.comp_county,\n" +
                "tcbi.comp_email,\n" +
                "tcbi.comp_kind,\n" +
                "tcbi.comp_name,\n" +
                "tcbi.comp_nature,\n" +
                "tcbi.comp_province\n" +
                "\n" +
                "FROM\n" +
                "\ttable_user tu\n" +
                "LEFT JOIN table_compbase_info tcbi on tu.u_acount = tcbi.u_acount\n" +
                "WHERE\n" +
                "tu.u_Identity = '4'");
        if(u_acount!=null&&!"".equals(u_acount)){
            sql.append("  and tu.u_acount = '"+u_acount+"'");
        }else{
            sql.append("  and tu.u_acount = "+0+"");
        }
        return sql.toString();
    }





    public String disableUser(String u_acount){
        StringBuilder sql = new StringBuilder();
        sql.append("update table_user set u_status = '2'");
        if(u_acount!=null&&!"".equals(u_acount)){
            sql.append("  where u_acount = '"+u_acount+"'");
        }else{
            sql.append("  where u_acount = "+0+"");
        }
        return sql.toString();
    }

    public String enableUser(String u_acount){
        StringBuilder sql = new StringBuilder();
        sql.append("update table_user set u_status = '1'");
        if(u_acount!=null&&!"".equals(u_acount)){
            sql.append("  where u_acount = '"+u_acount+"'");
        }else{
            sql.append("  where u_acount = "+0+"");
        }
        return sql.toString();
    }




    public String selectTeaByStuCode(String u_acount){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "\ttu.u_acount,\n" +
                "tti.tea_code,\n" +
                "tti.tea_department,\n" +
                "tti.tea_duties,\n" +
                "tti.tea_email,\n" +
                "tti.tea_name,\n" +
                "tti.tea_qq,\n" +
                "tu.u_status,\n" +
                "tu.u_acount,\n" +
                "tti.tea_schoolname,\n" +
                "tti.tea_tel\n" +
                "FROM\n" +
                "\ttable_user tu\n" +
                "RIGHT JOIN table_teabase_info tti on tu.u_acount = tti.u_acount\n" +
                "where u_Identity = '2' ");

        if(u_acount!=null&&!"".equals(u_acount)){
            sql.append("  and tu.u_acount = '"+u_acount+"'");
        }else{
            sql.append("  and tu.u_acount = "+0+"");
        }

        return sql.toString();
    }



    public String selectPeidata(String u_acount){
        StringBuilder sql = new StringBuilder();
        sql.append("select \n" +
                "case when u_Identity  = '1' then '学生'\n" +
                "     when u_Identity  = '2' then '教师'\n" +
                "     when u_Identity  = '4' then '企业'\n" +
                "else '学生' end as name,\n" +
                "\n" +
                "COUNT(u_acount) as value from table_user WHERE u_Identity != '3' group by u_Identity;");
        return sql.toString();
    }


    public String SelectEmploymentRanking(String u_acount){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "tssi.stu_department as name,\n" +
                "\tcount(tsji.u_acount) as value\n" +
                "FROM\n" +
                "\ttable_stujob_info tsji\n" +
                "LEFT JOIN table_stustatus_info  tssi\n" +
                "\n" +
                "ON\n" +
                "tsji.u_acount = tssi.u_acount\n" +
                "WHERE\n" +
                "\ttsji.stu_compjobstatus LIKE '就业%'\n" +
                "GROUP BY tssi.stu_department;");
        return sql.toString();
    }


    public String SelectEmploymentAddress(){
        StringBuilder sql = new StringBuilder();
        sql.append("select stu_compprovince as name,COUNT(u_acount) as value from table_stujob_info where stu_compjobstatus LIKE '就业%'  GROUP BY stu_compprovince ;");
        return sql.toString();
    }


    public String SelectcollegeProportionPei(){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT stu_department as name,COUNT(stu_department) AS value from table_stustatus_info group by stu_department;");
        return sql.toString();
    }


    public String SelectcollegeJobProportionPei(){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "  tssi.stu_department as name,\n" +
                "\tCOUNT(tssi.stu_department) as value\n" +
                "FROM\n" +
                "\ttable_stujob_info tsji \n" +
                "LEFT JOIN table_stustatus_info tssi\n" +
                "ON tsji.u_acount = tssi.u_acount\n" +
                "WHERE\n" +
                "\ttsji.stu_compjobstatus LIKE '就业%'\n" +
                "GROUP BY tssi.stu_department;");
        return sql.toString();
    }



    public String SelectcollegeFurtherProportionPei(){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "  tssi.stu_department as name,\n" +
                "\tCOUNT(tssi.stu_department) as value\n" +
                "FROM\n" +
                "\ttable_stujob_info tsji \n" +
                "LEFT JOIN table_stustatus_info tssi\n" +
                "ON tsji.u_acount = tssi.u_acount\n" +
                "WHERE\n" +
                "\ttsji.stu_compjobstatus LIKE '考研%' or tsji.stu_compjobstatus LIKE '升学%'\n" +
                "GROUP BY tssi.stu_department;");
        return sql.toString();
    }


    public String submitNewsPortal(NewsPortalDto newsPortalDto){
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into table_pressreleases_info(news_headline,news_subtitle,news_content,news_releasetime,u_acount) values( ");

        if(newsPortalDto.getNews_headline()!=null&&!"".equals(newsPortalDto.getNews_headline())){
            sql.append("'"+newsPortalDto.getNews_headline()+"',");
        }else{
            sql.append("'"+0+"',");
        }

        if(newsPortalDto.getNews_subtitle()!=null&&!"".equals(newsPortalDto.getNews_subtitle())){
            sql.append("'"+newsPortalDto.getNews_subtitle()+"',");
        }else{
            sql.append("'"+0+"',");
        }

        if(newsPortalDto.getNews_content()!=null&&!"".equals(newsPortalDto.getNews_content())){
            sql.append("'"+newsPortalDto.getNews_content()+"',");
        }else{
            sql.append("'"+0+"',");
        }

        if(newsPortalDto.getNews_releasetime()!=null&&!"".equals(newsPortalDto.getNews_releasetime())){
            sql.append("'"+newsPortalDto.getNews_releasetime()+"',");
        }else{
            sql.append("now(),");
        }

        if(newsPortalDto.getU_acount()!=null&&!"".equals(newsPortalDto.getU_acount())){
            sql.append("'"+newsPortalDto.getU_acount()+"'");
        }else{
            sql.append("'"+0+"'");
        }
        sql.append(")");
        return sql.toString();
    }



    public String selectAllNewsPortal(String news_headline){
        StringBuilder sql = new StringBuilder();
        sql.append("select news_headline,news_subtitle,news_releasetime,news_content,u_acount from table_pressreleases_info where 1=1");
        if(news_headline!=null&&!"".equals(news_headline)){
            sql.append(" and news_headline like '%"+news_headline+"%'");
        }
        sql.append("  group by news_headline ");
        sql.append("  order by news_releasetime desc;");
        return sql.toString();
    }


    public String selectAllemploymentRanking( ){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "\ttsji.stu_compname AS stucompname,\n" +
                "\ttsbi.stu_name AS stuname,\n" +
                "\ttssi.stu_department AS studepartment,\n" +
                "\ttssi.stu_profession AS stuprofession,\n" +
                "tssi.stu_schoolname AS stuschoolname\n" +
                "FROM\n" +
                "\ttable_stujob_info tsji\n" +
                "LEFT JOIN table_stubase_info tsbi ON tsji.u_acount = tsbi.u_acount\n" +
                "LEFT JOIN table_stustatus_info tssi ON tsji.u_acount = tssi.u_acount\n" +
                "WHERE\n" +
                "\ttsji.stu_compjobstatus LIKE '就业%'");

        return sql.toString();
    }


    public String selectAllentreprisesNews( ){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "                trji.recruit_address ,\n" +
                "                trji.recruit_job ,\n" +
                "                trji.recruit_material ,\n" +
                "                trji.recruit_name ,\n" +
                "                trji.recruit_people ,\n" +
                "                trji.recruit_peopletel ,\n" +
                "                trji.recruit_salary,\n" +
                "                trji.recruit_time,\n" +
                "                trji.recruit_updatetime,\n" +
                "\t\t\t\t\t\t\t\ttu.u_Identity\n" +
                "                FROM\n" +
                "                table_releasejob_info trji\n" +
                "                LEFT JOIN table_user tu on trji.u_acount = tu.u_acount\n" +
                "ORDER BY trji.recruit_updatetime DESC;");

        return sql.toString();
    }


    public String selectAllentreprisesNewsByName(String recruit_updatetimeStr, String recruit_name){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "\ttrji.recruit_address ,\n" +
                "\ttrji.recruit_job ,\n" +
                "\ttrji.recruit_material ,\n" +
                "\ttrji.recruit_name ,\n" +
                "\ttrji.recruit_people ,\n" +
                "\ttrji.recruit_peopletel ,\n" +
                "\ttrji.recruit_salary,\n" +
                "\ttrji.recruit_time,\n" +
                "\ttrji.recruit_updatetime\n" +
                "FROM\n" +
                "\ttable_releasejob_info trji\n" +
                "LEFT JOIN table_user tu on trji.u_acount = tu.u_acount\n" +
                        "where 1=1 \n"
                );
        if(recruit_name!=null&&!"".equals(recruit_name)){
            sql.append(" and trji.recruit_name = '"+recruit_name+"'");
        }else{
            sql.append(" and trji.recruit_name = '"+0+"'");
        }

        if(recruit_updatetimeStr!=null&&!"".equals(recruit_updatetimeStr)){
            sql.append(" and trji.recruit_updatetime = '"+recruit_updatetimeStr+"'");
        }else{
            sql.append(" and trji.recruit_updatetime = '"+0+"'");
        }
        sql.append(" group BY trji.recruit_updatetime ");
        sql.append(" ORDER BY trji.recruit_updatetime DESC;");
        return sql.toString();
    }

}


